*******************************************************************************
* COUNTY LEVEL
*******************************************************************************	

use dor_assessment_data_full.dta, clear

*------------------------------------------------------------------------------
* Merges
*------------------------------------------------------------------------------
* Assessments and Ratios
merge 1:1 countyfip year using assessment_sales_ratio_data
tab year
drop _merge
drop resid_ratio_code

* Fill panel for all years 1979-2018
tsset countyfip year
tsfill, full
sort countyfip year
tab year

* 1990 LER
merge m:1 countyfip using cntyler1990
drop _merge

* 1987 Free Lunch Eligibility & Special Ed
merge m:1 countyfip using cntyflespeced1987
drop _merge

* Free Lunch Eligibility
merge 1:1 countyfip year using cntyflepct19871998
drop _merge

* County Coal Data
merge 1:1 countyfip year using cntycoaldata, keepusing(coal_1990)
replace coal_1990 = 1 if coal_1990 ==.
drop _merge

*1980 Housing Value
merge m:1 countyfip using hv1980, keepusing(hv1980 hv1980_pctile hv1980_quartile)
drop if _merge==2
drop _merge

* Net Migration
merge 1:1 countyfip year using cntynetmig, keepusing(netmig)
drop if _merge==2
drop _merge

* Population
merge 1:1 countyfip year using kycountypop19871998, keepusing(pop)
drop _merge

* Enrollment
merge 1:1 countyfip year using cntyenrollmentccd, keepusing(enrollmentccd)
drop _merge

* PCE Deflator
merge m:1 year using pcepi
drop _merge

* County Aggregate Housing Value
merge 1:1 countyfip year using cntycensushv, keepusing(realppagghv)
drop if _merge==2
drop _merge

* 1990 Population density
merge 1:1 countyfip year using kycountycontrolvars19802018, keepusing(popdens)
drop _merge
preserve
	keep if year==1990
	keep countyfip popdens
	rename popdens popdens90
	tempfile popdens90
	save `popdens90'
restore
merge m:1 countyfip using `popdens90'
drop popdens _merge

* LERs
merge 1:1 countyfip year using kycnty_ler
drop _merge

xtset countyfip year

*------------------------------------------------------------------------------
* Variable Management
*------------------------------------------------------------------------------

* Fill missing time-invariant variables
foreach var of varlist fipsco county {
	replace `var'=`var'[_n-1] if `var'=="" & `var'[_n-1]!="" & countyfip==countyfip[_n-1]
}

foreach var of varlist er tech untreated treat {
	replace `var'=`var'[_n-1] if `var'==. & `var'[_n-1]!=. & countyfip==countyfip[_n-1]
}
gsort countyfip -year
foreach var of varlist er tech untreated treat {
	replace `var'=`var'[_n-1] if `var'==. & `var'[_n-1]!=. & countyfip==countyfip[_n-1]
}

sort countyfip year

* KERA Trend
gen postkeratr=0
replace postkeratr=year-1990 if year>1990

* Transformations
gen realdor = dor/pce2012
gen pprealdorccd=realdor/enrollmentccd
label var pprealdorccd "Real per-Pupil assess with CCD Enrollment"
gen logpprealdorccd=log(pprealdorccd)
gen logrealdor = log(realdor)
gen logenrollmentccd = log(enrollmentccd)
gen logpop=log(pop)
gen logrealppagghv = log(realppagghv)
gen lpopdens90=log(popdens90) 
gen logcoal_1990 = log(coal_1990)
egen logcoal_1990qrt=xtile(logcoal_1990), by(year) nq(4)
gen coal_cnty=0
replace coal_cnty=1 if logcoal_1990qrt==4
replace coal_cnty = . if year<=1984
gsort countyfip -year
replace coal_cnty = coal_cnty[_n-1] if coal_cnty==. & coal_cnty[_n-1]!=. & countyfip==countyfip[_n-1]

sort countyfip year

*------------------------------------------------------------------------------
* Merge Performance Audit Data
*------------------------------------------------------------------------------
merge 1:1 county year using performance_audit_clean
drop _merge

save pva_county_property, replace

*******************************************************************************
* SCHOOL DISTRICT LEVEL
*******************************************************************************

*------------------------------------------------------------------------------
* Merges
*------------------------------------------------------------------------------
use distenrollmentccd, clear

* KY District Numbers
merge m:1 ncesid using ncesid_kydistno_crosswalk
tab ncesid countyfip if _merge==1
drop if _merge==1
drop _merge

* F33 District Revenue
merge 1:1 ncesid year using kyf33_19871999
tab ncesid if _merge==2
drop if _merge==2
drop _merge

* KDE Revenue Data
merge 1:1 distno year using dist_revs_1990_2016, keepusing(totalrevs localrevs  staterevs fedrevs)
drop _merge

* Fill missing ncesid 
sort distno year
replace ncesid=ncesid[_n-1] if ncesid==. & ncesid[_n-1]!=. & distno==distno[_n-1]

* 1987 Free Lunch Eligibility & Special Ed
merge m:1 ncesid using distflespeced1987, /// 
	keepusing(distspecedshare1987 distfleshare1987 distspecedpctile1987 distflepctile1987 distspecedquartile1987 distflequartile1987)
drop if _merge==2
drop _merge

*1980 Housing Value
merge m:1 ncesid using distagghv1980, keepusing(hv1980 hv1980_pctile hv1980_quartile)
drop if _merge==2
drop _merge

* County Coal Data
merge m:1 countyfip year using cntycoaldata, ///
	keepusing(coal_1990)
drop if _merge==2
replace coal_1990 = 1 if coal_1990 ==.
drop _merge

* 1990 LER
merge m:1 ncesid using kydist_ler_1990
drop _merge

rename lerate lerate1990

* LERs
merge 1:1 ncesid year using kydist_ler
drop if _merge==2
drop _merge

* Treatment Variables
merge m:1 countyfip year using dor_assessment_data_full, ///
	keepusing(er tech untreated treat)
drop if _merge==2
drop _merge

* PCE Deflator
merge m:1 year using pcepi
drop if _merge==2
drop _merge

tab year

xtset ncesid year

*------------------------------------------------------------------------------
* Variable Management
*------------------------------------------------------------------------------
* Fill missing time-invariant variables
foreach var of varlist countyfip er tech untreated treat {
	replace `var'=`var'[_n-1] if `var'==. & `var'[_n-1]!=. & countyfip==countyfip[_n-1]
}
gsort countyfip -year
foreach var of varlist countyfip er tech untreated treat {
	replace `var'=`var'[_n-1] if `var'==. & `var'[_n-1]!=. & countyfip==countyfip[_n-1]
}
sort countyfip year

* KERA Trend
gen postkeratr=0
replace postkeratr=year-1990 if year>1990

* KDE deflate and convert to per pupil
foreach var of varlist totalrevs localrevs staterevs fedrevs {
	gen real`var' = `var'/pce2012
	gen realpp`var'ccd = real`var'/enrollmentccd
}

* F33 rescale, deflate, convert to per pupil
foreach x of varlist tcurelsc capspend tstrev tfedrev tlocrev proptax inctax othtax lunch othcharges interest misc {
replace `x'=`x'*1000
gen real`x'f33=`x'/pce2012
gen real`x'f33pp=`x'/enrollmentccd
}

* F33 Creating Imputed Values
foreach x of varlist realtcurelscf33pp realcapspendf33pp realtstrevf33pp realtlocrevf33pp realproptaxf33pp realinctaxf33pp realothtaxf33pp reallunchf33pp realothchargesf33pp realinterestf33pp realmiscf33pp {
bysort ncesid: ipolate `x' year, gen(i_`x')
replace i_`x'=. if i_`x'<0
}

* Tax rate percentile and quartile 
egen ler1990_pctile=xtile(lerate1990), nq(100) by(year)
egen ler1990_quartile=xtile(lerate1990), nq(4) by(year)

* Constrained tax rate dummy
gen ler1990_le30 = (lerate1990 < 30)

* Coal measure and coal county dummy
gen logcoal_1990 = log(coal_1990)
egen logcoal_1990qrt=xtile(logcoal_1990), by(year) nq(4)
gen coal_cnty=0
replace coal_cnty=1 if logcoal_1990qrt==4

* Logs
gen logenrollmentccd=log(enrollmentccd)
gen logrealpplocalrevsccd=log(realpplocalrevsccd)
gen logreallocalrevs = log(realpplocalrevsccd)
gen logrealtlocrevf33pp=log(realtlocrevf33pp)
gen logi_realtlocrevf33pp=log(i_realtlocrevf33pp)
gen logrealproptaxf33pp=log(realproptaxf33pp)
gen logi_realproptaxf33pp=log(i_realproptaxf33pp)

foreach x of varlist i_realinctaxf33pp i_realothtaxf33pp i_reallunchf33pp i_realothchargesf33pp i_realinterestf33pp i_realmiscf33pp {
gen log`x'=log(`x'+1)
}

save pva_district_rev, replace

	